package y_ut.dbtools;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

/**
 * 
 * @author youxz
 *
 */
public class DBTool {
	
	private static Logger logger = Logger.getLogger(DBTool.class);
	
	
	private  DBTool() {
		
	}
	/**
	 * 根据schema获取所有表名称
	 * @param schema
	 * @return
	 */
	public static String [] getTableNames(Connection conn,String schema){
	 	try {
			DatabaseMetaData dbmd = conn.getMetaData();
			ResultSet rs = dbmd.getTables(null, schema, null, new String[]{"TABLE"});
			List<String> tmpNames = new ArrayList<String>();
			while(rs.next()){
				String tableName = rs.getString("TABLE_NAME");
				tmpNames.add(tableName);
			}
			rs.close();
			String [] tableNames = new String[tmpNames.size()];
			tmpNames.toArray(tableNames);
			return tableNames;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 获取表记录数
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static int getTableRecordCount(Connection conn,String tableName) throws SQLException{
		String sql = "SELECT COUNT(*) FROM "+tableName;
		Statement state = conn.createStatement();
		ResultSet rs =  state.executeQuery(sql);
		if(rs.next()){
			return rs.getInt(1);
		}
		rs.close();
		state.close();
		return 0;
	}
	/**
	 * 获取表所有列名称
	 * @param rs
	 * @return
	 */
	public static String[] getColumnNames(ResultSet rs){
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCnt = rsmd.getColumnCount();
			String []colNames = new String[colCnt];
			for(int i=0;i<colCnt;i++){
				colNames[i] = rsmd.getColumnLabel(i+1);
			}
			return colNames;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 获取列类型
	 * @param rs
	 * @return
	 */
	public static int[] getColumnTypes(ResultSet rs){
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCnt = rsmd.getColumnCount();
			int []colTypes = new int[colCnt];
			for(int i=0;i<colCnt;i++){
				colTypes[i] = rsmd.getColumnType(i+1);
			}
			return colTypes;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return null;
	}
	
	public static int[] getColumnTypes(ResultSet rs,String []colNames){
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int []colTypes = new int[colNames.length];
			String []_colNames = getColumnNames(rs);
			outer:
			for(int i=0;i<colNames.length;i++){
				
				for(int s=0;s<_colNames.length;s++){
					if(colNames[i].equalsIgnoreCase(_colNames[s])){
						colTypes[i] = rsmd.getColumnType(s+1);
						continue outer;
					}
				}
				throw new SQLException("NOT FIND ColumnName:"+colNames[i]);
			}
			return colTypes;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 获取列类型名称
	 * @param rs
	 * @return
	 */
	public static String[] getColumnTypeNames(ResultSet rs){
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCnt = rsmd.getColumnCount();
			String []colTypes = new String[colCnt];
			for(int i=0;i<colCnt;i++){
				colTypes[i] = rsmd.getColumnTypeName(i+1);
			}
			return colTypes;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return null;
	}
	
	public static Connection connectSQL(String driver,String url,String UserName,String Password){
		try{
			Class.forName(driver).newInstance();
			return DriverManager.getConnection(url, UserName, Password);
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	public static String generate(String tableName,String []colNames){
		StringBuffer cols = new StringBuffer();
		StringBuffer vals = new StringBuffer();
		
		for(int i=0;i<colNames.length;i++){
			if(i>0) {
				cols.append(",");
				vals.append(",");
			}
			cols.append(colNames[i]);
			vals.append("?");
		}
		
		
		return String.format("INSERT INTO %s (%s)VALUES(%s)", tableName,cols.toString(),vals.toString());
	}
	
	public static void setParams(PreparedStatement pstate,int []colTypes,String []values) throws SQLException{
		for(int i=0;i<colTypes.length;i++){
			if(values[i] == null || values[i].length()==0) {
				pstate.setNull(i+1, colTypes[i]);
				continue;
			}
			switch (colTypes[i]) {
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
			case Types.LONGNVARCHAR:
			case Types.DATE:
			case Types.TIME:
			case Types.TIMESTAMP:
			case Types.CLOB:
					pstate.setString(i+1, values[i]);
				break;
			case Types.SMALLINT:
					pstate.setInt(i+1, Short.parseShort(values[i]));
				break;
			case Types.INTEGER:
			case Types.BIGINT:
					pstate.setInt(i+1, Integer.parseInt(values[i]));
				break;
			case Types.BLOB:
/*			case Types.CLOB:*/
					try {
						pstate.setBytes(i+1, new BASE64Decoder().decodeBuffer(values[i]));
					} catch (IOException e) {
						logger.error(e);
						e.printStackTrace();
						pstate.setNull(i+1, colTypes[i]);
					}
				break;
			default:
				pstate.setString(i+1, values[i]);
				break;
			}
			
		}
	}
	
	public static void getValues(ResultSet rs,int []colTypes,String [] colNames,String []rcValues) throws SQLException{
		InputStream is = null;
		ByteArrayOutputStream bao=null;
		byte buf[] = new byte[1024];
		int len = 0;
		for(int s=0;s<colNames.length;s++){
			switch (colTypes[s]) {
			case Types.BLOB:
				Blob blob = rs.getBlob(colNames[s]);
				if(blob == null){
					rcValues[s]=null;
					break;
				}
				is = blob.getBinaryStream();
				bao = new ByteArrayOutputStream();
				try{
				while(true){
					len = is.read(buf);
					if(len==-1) break;
					bao.write(buf,0,len);
				}
				bao.close();
				rcValues[s] = new BASE64Encoder().encode(bao.toByteArray()).replaceAll("\n", "").replaceAll("\r", "");
				
				}catch(IOException e){
					logger.error(e);
					rcValues[s] =null;
					e.printStackTrace();
				}
				
				break;
/*			case Types.CLOB:
				Clob clob = rs.getClob(colNames[s]);
				if(clob == null){
					rcValues[s]=null;
					break;
				}
				is = clob.getAsciiStream();
				bao = new ByteArrayOutputStream();
				
				
				try{
				while(true){
					len = is.read(buf);
					if(len==-1) break;
					bao.write(buf,0,len);
				}
				bao.close();
				rcValues[s] = new BASE64Encoder().encode(bao.toByteArray()).replaceAll("\n", "").replaceAll("\r", "");
				}catch(IOException e){
					logger.error(e);
					rcValues[s] =null;
					e.printStackTrace();
				}
				break;*/

			default:
				rcValues[s] = rs.getString(colNames[s]);
				break;
			}
			
		}
	}
}
